﻿Imports System.Data.OleDb

Module XL_CTCaThe
    Structure CTCaThe
        Private _iD As Integer
        Property ID() As Integer
            Get
                Return _iD
            End Get
            Set(ByVal value As Integer)
                _iD = value
            End Set
        End Property
        Private _ngay As Date
        Property Ngay() As Date
            Get
                Return _ngay
            End Get
            Set(ByVal value As Date)
                _ngay = value
            End Set
        End Property
        Private _maTD As Integer

        Property MaTD() As Integer
            Get
                Return _maTD
            End Get
            Set(ByVal value As Integer)
                _maTD = value
            End Set
        End Property
       
        Private _soTien As Integer
        Property Sotien() As Integer
            Get
                Return _soTien
            End Get
            Set(ByVal value As Integer)
                _soTien = value
            End Set
        End Property
        
        Private _moTa As String
        Property MoTa() As String
            Get
                Return _moTa
            End Get
            Set(ByVal value As String)
                _moTa = value
            End Set
        End Property
    End Structure

    Public Function LayDS(ByVal mathe As Integer) As List(Of CTCaThe)
        Dim ds As New List(Of CTCaThe)
        Try
            Dim con = DataProvider.MoKetNoi()
            con.Open()
            Dim cmd As OleDbCommand = New OleDbCommand("Select * from CTCaThe where MaTD=@MaTD", con)
            cmd.Parameters.AddWithValue("@MaTD", mathe)
            Dim rd As OleDbDataReader = cmd.ExecuteReader()
            While rd.Read()
                Dim p As CTCaThe = New CTCaThe()
                p.ID = rd.GetInt32(0)
                p.Ngay = rd.GetDateTime(1)
                p.MaTD = rd.GetInt32(2)
                p.Sotien = rd.GetInt32(3)
                p.MoTa = rd.GetString(4)
                ds.Add(p)
            End While
            con.Close()
            Return (ds)
        Catch ex As Exception
            Return ds
        End Try
    End Function

    Public Function LayDS2(ByVal mathe As Integer, ByVal t1 As Date, ByVal t2 As Date) As List(Of CTCaThe)
        Dim ds As New List(Of CTCaThe)
        Try
            Dim con = DataProvider.MoKetNoi()
            con.Open()
            Dim cmd As OleDbCommand = New OleDbCommand("Select * from CTCaThe where MaTD=@MaTD and Ngay>=@t1 and Ngay<=@t2", con)
            cmd.Parameters.AddWithValue("@MaTD", mathe)
            cmd.Parameters.AddWithValue("@t1", t1.Date)
            cmd.Parameters.AddWithValue("@t2", t2.Date)
            Dim rd As OleDbDataReader = cmd.ExecuteReader()
            While rd.Read()
                Dim p As CTCaThe = New CTCaThe()
                p.ID = rd.GetInt32(0)
                p.Ngay = rd.GetDateTime(1)
                p.MaTD = rd.GetInt32(2)
                p.Sotien = rd.GetInt32(3)
                p.MoTa = rd.GetString(4)
                ds.Add(p)
            End While
            con.Close()
            Return (ds)
        Catch ex As Exception
            Return ds
        End Try
    End Function

    Public Function Lay(ByVal id As Integer) As CTCaThe
        Try
            Dim con = DataProvider.MoKetNoi()
            con.Open()
            Dim cmd As OleDbCommand = New OleDbCommand("Select * from CTCaThe where ID=@ID", con)
            cmd.Parameters.AddWithValue("@ID", id)
            Dim rd As OleDbDataReader = cmd.ExecuteReader()
            Dim p As CTCaThe = New CTCaThe()
            If rd.Read() Then
                p.ID = rd.GetInt32(0)
                p.Ngay = rd.GetDateTime(1)
                p.MaTD = rd.GetInt32(2)
                p.Sotien = rd.GetInt32(3)
                p.MoTa = rd.GetString(4)
            End If
            con.Close()
            Return p
        Catch ex As Exception
            Return Nothing
        End Try
    End Function

    Public Function No(ByVal nd As Integer) As Integer
        Try
            Dim kq As Integer = 0
            Dim con = DataProvider.MoKetNoi()
            con.Open()
            Dim cmd1 As OleDbCommand = New OleDbCommand("select sum(SoTien) from CTCaThe where MaTD=@nd", con)
            cmd1.Parameters.AddWithValue("@nd", nd)
            kq = cmd1.ExecuteScalar()

            con.Close()
            Return kq
        Catch ex As Exception
            Return 0
        End Try
    End Function

    Public Function No2(ByVal nd As Integer, ByVal d1 As Date, ByVal d2 As Date) As Integer
        Try
            Dim kq As Integer = 0
            Dim con = DataProvider.MoKetNoi()
            con.Open()
            Dim cmd1 As OleDbCommand = New OleDbCommand("select sum(SoTien) from CTCaThe where MaTD=@nd and Ngay>=@t1 and Ngay<=@t2", con)
            cmd1.Parameters.AddWithValue("@nd", nd)
            cmd1.Parameters.AddWithValue("@t1", d1.Date)
            cmd1.Parameters.AddWithValue("@t2", d2.Date)
            kq = cmd1.ExecuteScalar()

            con.Close()
            Return kq
        Catch ex As Exception
            Return 0
        End Try
    End Function

    Public Function Them(ByRef p As CTCaThe) As Integer
        Try
            Dim con = DataProvider.MoKetNoi()
            con.Open()
            Dim cmd As OleDbCommand = New OleDbCommand("insert into CTCaThe(Ngay, MaTD, SoTien, MoTa) values(@Ngay, @MaTD, @SoTien, @MoTa)", con)
            cmd.Parameters.AddWithValue("@Ngay", p.Ngay)
            cmd.Parameters.AddWithValue("@MaTD", p.MaTD)
            cmd.Parameters.AddWithValue("@SoTien", p.Sotien)
            cmd.Parameters.AddWithValue("@MoTa", p.MoTa)
            Dim kq = cmd.ExecuteNonQuery()
            cmd.CommandText = "select @@IDENTITY"
            p.ID = cmd.ExecuteScalar()
            con.Close()
            Return kq
        Catch ex As Exception
            Return False
        End Try
    End Function

    Public Function Xoa(ByVal p As CTCaThe) As Integer
        Try
            Dim con = DataProvider.MoKetNoi()
            con.Open()
            Dim cmd As OleDbCommand = New OleDbCommand("delete from CTCaThe where ID = @ID", con)
            cmd.Parameters.AddWithValue("@ID", p.ID)
            Dim kq = cmd.ExecuteNonQuery()

            con.Close()
            Return kq
        Catch ex As Exception
            Return False
        End Try
    End Function

    Public Function CapNhat(ByVal p As CTCaThe) As Boolean
        Try
            Dim con = DataProvider.MoKetNoi()
            con.Open()
            Dim cmd As OleDbCommand = New OleDbCommand("update CTCaThe set Ngay=@Ngay, MaTD=@MaTD, SoTien=@SoTien, MoTa=@MoTa where ID = @ID", con)
            cmd.Parameters.AddWithValue("@Ngay", p.Ngay)
            cmd.Parameters.AddWithValue("@MaTD", p.MaTD)
            cmd.Parameters.AddWithValue("@SoTien", p.Sotien)
            cmd.Parameters.AddWithValue("@MoTa", p.MoTa)
            cmd.Parameters.AddWithValue("@ID", p.ID)
            Dim kq = cmd.ExecuteNonQuery()
            
            con.Close()
            Return kq
        Catch ex As Exception
            Return False
        End Try
    End Function

    'Public Function TienThang(ByVal the As Integer, ByVal ngay1 As Date, ByVal ngay2 As Date) As Integer
    '    Try
    '        Dim con = DataProvider.MoKetNoi()
    '        con.Open()
    '        Dim cmd As OleDbCommand = New OleDbCommand("select sum(SoTien) from CTCaThe where MaTD=@MaTD and Ngay>=@ngay1 and Ngay<@ngay2", con)
    '        cmd.Parameters.AddWithValue("@MaTD", the)
    '        cmd.Parameters.AddWithValue("@ngay1", ngay1)
    '        cmd.Parameters.AddWithValue("@ngay2", ngay2)
    '        Dim kq = cmd.ExecuteScalar()
    '        con.Close()
    '        Return kq
    '    Catch ex As Exception
    '        Return -1
    '    End Try
    'End Function
End Module
